package com.jdbc;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class TestJDBC {
    public static void main(String[] args) throws InterruptedException {
        querySQL();
        insertSQL();
        updateSQL();
        deleteSQL();
    }

    private static void updateSQL() {
        ApplicationContext ctx = new ClassPathXmlApplicationContext("spring_cfg_JDBC.xml");
        JdbcTemplate jdbcTemplate = (JdbcTemplate) ctx.getBean("jdbcTemplate");
        StuInfo stu = new StuInfo();
        stu.setStuID("201831221004");
        stu.setTelNum("200000");
        stu.setNickName("烟雨江南");
        stu.setSex("男");
        Date d = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        stu.setBirthday(sdf.format(d));
        String sql = "update web-design.stu_account" + " set " + "tel_num=" + stu.getTelNum() + " ," + "nickname='" + stu.getNickName() + "' ," + "sex='" + stu.getSex() + "' ," + "birthday='" + stu.getBirthday() + "' " + "where stuID=" + stu.getStuID();
        printStuInfo(stu);
        int rel = jdbcTemplate.update(sql);
        System.out.println(rel);

    }

    private static void printStuInfo(StuInfo stu) {
        System.out.println("学号：" + stu.getStuID());
        System.out.println("班级：" + stu.getClsID());
        System.out.println("手机：" + stu.getTelNum());
        System.out.println("姓名：" + stu.getUserName());
        System.out.println("昵称：" + stu.getNickName());
        System.out.println("职务：" + stu.getPost());
        System.out.println("性别：" + stu.getSex());
        System.out.println("密码：" + stu.getPassword());
        System.out.println("生日：" + stu.getBirthday());
        System.out.println("注册日期：" + stu.getRegisterTime());
    }

    private static void deleteSQL() {
        ApplicationContext ctx = new ClassPathXmlApplicationContext("spring_cfg_JDBC.xml");
        JdbcTemplate jdbcTemplate = (JdbcTemplate) ctx.getBean("jdbcTemplate");
        String sql = "delete from web-design.stu_schedule where keyID = ";
        for (int i = 0; i < 13; i++) {
            int line = jdbcTemplate.update(sql + i);
            System.out.println("Line(s): " + line);
        }
    }

    private static void insertSQL() throws InterruptedException {
        ApplicationContext ctx = new ClassPathXmlApplicationContext("spring_cfg_JDBC.xml");
        JdbcTemplate jdbcTemplate = (JdbcTemplate) ctx.getBean("jdbcTemplate");
        String sql = "insert into web-design.stu_schedule (stuID, content, addTime, endTime) values(?,?,?,?)";
        String[] con = new String[]{"CET4", "教资", "期末", "跑步", "写作业"};
        for (int i = 0; i < 5; i++) {
            Thread.sleep(5000);
            StuSchedule schedule = new StuSchedule();
            Date d = new Date();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String addTime = sdf.format(d);
            schedule.setStuID("20183122100" + (i + 1));
            schedule.setAddTime(addTime);
            schedule.setEndTime(addTime);
            schedule.setContent(con[i]);
            jdbcTemplate.update(sql, schedule.getStuID(), schedule.getContent(), schedule.getStuID(), schedule.getEndTime());
        }
    }

    private static void querySQL() {

        ApplicationContext ctx = new ClassPathXmlApplicationContext("spring_cfg_JDBC.xml");
        JdbcTemplate jdbcTemplate = (JdbcTemplate) ctx.getBean("jdbcTemplate");
        String sql = "select * from student";
        method_1(jdbcTemplate, sql);
        sql = "select * from student where name=?"; //这种方法得添加 new objects[]={"zs}
        method_2(jdbcTemplate, sql);
        method_3(jdbcTemplate, sql);
    }

    // 条件查询一个
    private static void method_3(JdbcTemplate jdbcTemplate, String sql) {
        System.out.println("方法 三：");
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, "王雨");
        System.out.println("listSize = " + list.size());
        list.forEach(map -> {
            System.out.print("姓名：" + map.get("name"));
            System.out.println("\t性别：" + map.get("sex"));
        });
    }

    // 条件查询一个
    private static void method_2(JdbcTemplate jdbcTemplate, String sql) {
        System.out.println("方法 二：");
        //封装到指定的类
        List<Student> list = jdbcTemplate.query(sql, new Object[]{"zs"}, new BeanPropertyRowMapper<>(Student.class));
        System.out.println("listSize = " + list.size());
        list.forEach(student -> {
            System.out.print("姓名：" + student.getName());
            System.out.println("\t性别：" + student.getSex());
        });

        Student student = list.get(0);
        System.out.print("姓名：" + student.getName());
        System.out.println("\t性别：" + student.getSex());
    }

    // 查找所有
    private static void method_1(JdbcTemplate jdbcTemplate, String sql) {
        System.out.println("方法 一：");
        List<Student> list = jdbcTemplate.query(sql, (resultSet, i) -> {
            Student student = new Student();
            student.setName(resultSet.getString("name"));
            student.setSex(resultSet.getString("sex"));
            return student;
        });
        System.out.println("listSize = " + list.size());
        list.forEach(student -> {
            System.out.print("姓名：" + student.getName());
            System.out.println("\t性别：" + student.getSex());
        });
    }
}